package com.jdbc_.pichuli;

import com.jdbc_.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo {

    // 传统方法，添加5000条数据
    @Test
    public void noBatch() throws SQLException {
        // 获取连接
        Connection connection = JDBCUtils.getConnection();

        System.out.println("开始执行");
        long start = System.currentTimeMillis();
        // 执行SQL
        String sql = "insert into admin2 values (null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "123");
            preparedStatement.executeUpdate();
        }
        System.out.println("结束执行");
        long end = System.currentTimeMillis();
        System.out.println("传统方式耗时=" + (end - start) + "毫秒");  // 1512396

        // 关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }


    // 批处理，添加5000条数据
    // 【注】：url必须要改了，增加?rewriteBatchedStatements=true
    @Test
    public void batch() throws SQLException {
        // 获取连接
        Connection connection = JDBCUtils.getConnection();

        System.out.println("开始执行");
        long start = System.currentTimeMillis();
        String sql = "insert into admin2 values (null,?,?)";
        // 执行sql
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "Jack" + i);
            preparedStatement.setString(2, "123");

            // 先不执行SQL，而是将SQL语句先加入到批处理包中
            preparedStatement.addBatch();
            // 然后，每当满1000条语句，再批量执行
            if ((i + 1) % 1000 == 0) {
                // 此时，批量执行sql
                preparedStatement.executeBatch();
                // 必须清空一下批处理包，方便下一个1000条数据进入
                preparedStatement.clearBatch();
            }
        }
        System.out.println("结束执行");
        long end = System.currentTimeMillis();
        System.out.println("批处理方式耗时=" + (end - start) + "毫秒");  // 393

        // 关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }
}
